//
//  TraineeCourseTable.m
//  TMS
//
//  Created by Dhanashree Bodas on 30/01/15.
//  Copyright (c) 2015 Dhanashree Bodas. All rights reserved.
//

#import "TraineeCourseTable.h"

@implementation TraineeCourseTable


-(int) allocateCourse:(int)tid courseName:(NSArray *)course andDate:(NSString *)date
{

    self.db=[[DBManager alloc] initWithDatabaseFilename:@"tmsdb.sql"];

    for(int i=0;i<course.count;i++)
    {
        Course *c=[course objectAtIndex:i];
        int cid=c.cid;
         NSString *query=[NSString stringWithFormat:@"insert into Trainee_Course values('%d','%d','%d','%@','%@')",cid,tid,0,nil,date];
        [self.db executeQuery:query];
        self.db.affectedRows++;
        
    }
    NSLog(@"allocation count=%d",self.db.affectedRows);
    return  self.db.affectedRows;
}


-(NSArray *) getAllocations:(int)tid
{
    
    self.db=[[DBManager alloc] initWithDatabaseFilename:@"tmsdb.sql"];
    NSString *query=[NSString stringWithFormat:@"select Course.coursename from Course,Trainee_Course where Course.cid=Trainee_Course.cid and Trainee_Course.tid='%d' and Trainee_Course.flag='%d'",tid,1];
    NSArray *adata=[self.db loadDataFromDB:query];
    return adata;
}



-(NSArray *) listUnallocatedCourse:(int)tid{
    
    self.db=[[DBManager alloc]initWithDatabaseFilename:@"tmsdb.sql"];
    
    NSString *query1=[NSString stringWithFormat:@"select count(cid) from Trainee_Course where tid='%d'",tid];
    
    NSArray *codata=[self.db loadDataFromDB:query1];
    
    int count=[[[codata objectAtIndex:0] objectAtIndex:0 ]intValue];
    NSLog(@"%d",count);
    
    if(count>0)
    {
        query1=[NSString stringWithFormat:@"select distinct Course.cid,Course.coursename,Course.description,Course.platform from Course,Trainee_Course where Trainee_Course.tid='%d' and Course.cid not in(select Trainee_Course.cid from Trainee_Course where Trainee_Course.tid='%d') order by Course.cid ",tid,tid];
    }
    else
    {
        query1=@"select * from Course";
        
    }
    
    NSArray *courseData=[self.db loadDataFromDB:query1];
    NSMutableArray *cdata = [[NSMutableArray alloc] init];
    
    for(int i=0;i<courseData.count;i++)
    {
        
        Course *c=[[Course alloc]init];
        id temp = [courseData objectAtIndex:i];
        c.cid = [[temp objectAtIndex:[self.db.arrColumnNames indexOfObject:@"cid"] ] intValue];
        c.cname=[temp objectAtIndex:[self.db.arrColumnNames indexOfObject:@"coursename"]];
        c.cdesc=[temp objectAtIndex:[self.db.arrColumnNames indexOfObject:@"description"]];
        c.platform=[temp objectAtIndex:[self.db.arrColumnNames indexOfObject:@"platform"]];
        [cdata addObject:c];
    }
    return cdata;
    
    
}


-(NSArray*) getRejectedCourses:(int)tid
{
    DBManager *db=[[DBManager alloc]initWithDatabaseFilename:@"tmsdb.sql"];
    NSMutableArray *data = [[NSMutableArray alloc] init];
    
    
    NSString *query1=[NSString stringWithFormat:@"select distinct Course.cid,Course.coursename,Course.description,Course.platform,Trainee_Course.reason from Course,Trainee_Course where Course.cid=Trainee_Course.cid and Trainee_Course.tid='%d'and Trainee_Course.flag=2 and Trainee_Course.reason!='null'",tid];
    NSArray *rdata=[db loadDataFromDB:query1];
    
    for(int i=0;i<rdata.count;i++)
    {
        
        Course *c=[[Course alloc]init];
        id temp = [rdata objectAtIndex:i];
        c.cid = [[temp objectAtIndex:[db.arrColumnNames indexOfObject:@"cid"] ] intValue];
        c.cname=[temp objectAtIndex:[db.arrColumnNames indexOfObject:@"coursename"]];
        c.cdesc=[temp objectAtIndex:[db.arrColumnNames indexOfObject:@"description"]];
        c.platform=[temp objectAtIndex:[db.arrColumnNames indexOfObject:@"platform"]];
        
        TraineeCourse *tc=[[TraineeCourse alloc]init];
        tc.tid=tid;
        tc.cid=[[temp objectAtIndex:[db.arrColumnNames indexOfObject:@"cid"] ] intValue];
        tc.flag=2;
        tc.date=nil;
        tc.reason=[temp objectAtIndex:[db.arrColumnNames indexOfObject:@"reason"]];
        [data addObject:c];
        [data addObject:tc];
    }
    NSLog(@"array : %@",data);
    return data;
    
}


//Trainee Module


//This method is to display all the courses allocated to him/her by the admin for the "current date"
-(NSArray *) LoadCoursesWithTraineeID:(int)tid andDate:(NSString *)date{
    
    // Form the query.
    
    NSString *query = [NSString stringWithFormat:@"select Course.cid,Course.coursename,Trainee_Course.flag from Trainee_Course,Course where Trainee_Course.tid=%d and Trainee_Course.cid=Course.cid and Trainee_Course.date='%@'",tid,date];
    
    NSArray *arrCourse=[[NSArray alloc] init];
    self.db=[[DBManager alloc] initWithDatabaseFilename:@"tmsdb.sql"];
    
    arrCourse=[self.db loadDataFromDB:query];
    
    NSLog(@"In TraineeCourseTable trainee id is=%d",tid);
    
    return arrCourse;
    
}

//This method is a for the trainee if he/she has rejected any course which is already accepted by him/her
-(int) onRejectwithCourseId:(int)cid andTraineeID:(int)tid andStatus:(int)flag andReason:(NSString *)reason
{
    
    //Form the query
    
    NSString *query=[NSString stringWithFormat:@"update Trainee_Course set flag=%d,reason='%@' where cid=%d and tid=%d;",flag,reason,cid,tid];
    
    if(!self.db)
    {
        self.db=[[DBManager alloc] initWithDatabaseFilename:@"tmsdb.sql"];
    }
    [self.db executeQuery:query];
    
    // If the query was successfully executed then pop the view controller in the view part
    if (self.db.affectedRows != 0)
    {
        NSLog(@"In Course Table Query For Reject was executed successfully. Affected rows = %d", self.db.affectedRows);
        return 1;
    }
    else
    {
        NSLog(@"In Course Table Could not execute the query.");
        return 0;
    }
    
}


//This method is for trainee to accept a course if he/she has not accepted any courses from the list allocated to him
-(int) onAcceptwithCourseId:(int)cid andTraineeID:(int)tid andStatus:(int)flag andReason:(NSString *)reason
{
    
    //Form the query
    NSString *query=[NSString stringWithFormat:@"update Trainee_Course set flag=%d,reason='%@' where cid=%d and tid=%d;",flag,reason,cid,tid];
    if(!self.db)
    {
        self.db=[[DBManager alloc] initWithDatabaseFilename:@"tmsdb.sql"];
    }
    [self.db executeQuery:query];
    
    // If the query was successfully executed then pop the view controller in the view part
    if (self.db.affectedRows != 0) {
        NSLog(@"In Course Table Query For Accept was executed successfully. Affected rows = %d", self.db.affectedRows);
        
        return 1;
    }
    else{
        NSLog(@"Could not execute the query.");
        return 0;
    }
}

//This method is to check whether any other course is already accepted by the trainee(for only one course selection)
-(NSArray *) onAcceptCheckExistingCourseWithTid:(int)tid andStatus:(int)flag andDate:(NSString *)date
{
    //Form the query
    NSString *query=[NSString stringWithFormat:@"select Trainee_Course.cid,Course.coursename from Trainee_Course,Course where Trainee_Course.tid=%d and Trainee_Course.cid=Course.cid and Trainee_Course.flag=%d and date='%@';",tid,flag,date];
    if(!self.db)
    {
        self.db=[[DBManager alloc] initWithDatabaseFilename:@"tmsdb.sql"];
    }
    
    NSArray *arrCourseSelected=[[NSArray alloc]init];
    arrCourseSelected=[self.db loadDataFromDB:query];
    
    return arrCourseSelected;
    
}

//This method is to load course whch are already accepted by the trainee previously not on the current day
-(NSArray *) LoadCoursesWithTraineeIdAlreadyDone:(int)tid andDate:(NSString *)date andStatus:(int)flag
{
    
    //Form the query
    NSString * query=[NSString stringWithFormat:@"select Course.coursename,Course.platform,Trainee_Course.date from Course,Trainee_Course where Trainee_Course.cid=Course.cid and date!='%@' and Trainee_Course.tid=%d and Trainee_Course.flag=%d;",date,tid,flag];
    
    
    NSArray *arrCourse=[[NSArray alloc] init];
    self.db=[[DBManager alloc] initWithDatabaseFilename:@"tmsdb.sql"];
    
    arrCourse=[self.db loadDataFromDB:query];
    
    NSLog(@"In TraineeCourseTable trainee id is=%d",tid);
    
    return arrCourse;
}


@end
